Module Module_Report

    Function GetData_SendCarLest(ByVal p_StartDate As String, ByVal p_StopDate As String, ByVal p_UnitCode As String, ByVal p_UserId As String) As DataSet
        Dim StrSql As String = ""
        Dim Wss As New Carres_Webservice.PTT_CARRESERVE_WEBSERVICE
        Dim Ds_SendCarLest As New DataSet
        Dim ii As Integer
        Dim iCountDs As Integer
        Wss.Url = iPAddressServerWebService
        StrSql = StrSql & " SELECT * FROM("
        StrSql = StrSql & " SELECT     "
        StrSql = StrSql & " 	InvReserveDataCarDriver.BdNumber, "
        StrSql = StrSql & " 	InvReserveDataCarDriver.ReserveId, "
        StrSql = StrSql & " 	InvReserveInfo.UserId, "
        StrSql = StrSql & " 	'' As UserName,"
        StrSql = StrSql & " 	InvReserveInfo.UnitId, "
        StrSql = StrSql & " 	'' As UnitName,"
        StrSql = StrSql & " 	InvReserveInfo.Area, "
        StrSql = StrSql & " 	InvReserveInfo.Description, "
        StrSql = StrSql & " 	InvCarDataType.DataTypeName, "
        StrSql = StrSql & " 	InvDataCar.CarNumber, "
        StrSql = StrSql & " 	ISNULL(InvDataDriver.DriverName,'-') AS DriverName , "
        StrSql = StrSql & " 	InvReserveInfo.StartDate, "
        StrSql = StrSql & " 	InvReserveInfo.StopDate,"
        StrSql = StrSql & " 	(Select Top 1 ITCW.ReserveDay From InvReserveTimeClostWork ITCW Where (ITCW.BdNumber = InvReserveDataCarDriver.BdNumber)ORder by DayId asc) AS StartDateSend,"
        StrSql = StrSql & " 	(Select Top 1 ITCW.ReserveDay From InvReserveTimeClostWork ITCW Where (ITCW.BdNumber = InvReserveDataCarDriver.BdNumber)ORder by DayId desc) AS StopDateSend,"
        StrSql = StrSql & " 	(Select Count(*) From InvReserveDataTime Where (InvReserveDataCarDriver.ReserveId = InvReserveDataTime.ReserveId)) AS CountDayAmountRes,"
        StrSql = StrSql & " 	(Select Count(distinct ITCW.ReserveDay) From InvReserveTimeClostWork ITCW Where (ITCW.BdNumber = InvReserveDataCarDriver.BdNumber)) AS CountDayAmountSend"
        StrSql = StrSql & " FROM         "
        StrSql = StrSql & " 	InvReserveInfo INNER JOIN"
        StrSql = StrSql & " 	InvReserveDataCarDriver ON InvReserveInfo.ReserveId = InvReserveDataCarDriver.ReserveId LEFT OUTER JOIN"
        StrSql = StrSql & " 	InvDataDriver ON InvReserveDataCarDriver.DriverCode = InvDataDriver.DriverCode RIGHT OUTER JOIN"
        StrSql = StrSql & " 	InvCarDataType INNER JOIN"
        StrSql = StrSql & " 	InvDataCar ON InvCarDataType.DataTypeID = InvDataCar.DataTypeId INNER JOIN"
        StrSql = StrSql & " 	InvCarColor ON InvDataCar.ColorId = InvCarColor.ColorId INNER JOIN"
        StrSql = StrSql & " 	InvCarBrand ON InvDataCar.BrandId = InvCarBrand.BrandId INNER JOIN"
        StrSql = StrSql & " 	InvCarSize ON InvDataCar.SizeId = InvCarSize.SizeId ON InvReserveDataCarDriver.CarId = InvDataCar.CarId LEFT OUTER JOIN"
        StrSql = StrSql & " 	InvOwner ON InvDataCar.OwnerId = InvOwner.OwnerId"
        StrSql = StrSql & " WHERE"
        StrSql = StrSql & " 	(InvReserveDataCarDriver.CostWorkStatus = 1)"
        StrSql = StrSql & " ) TMP_DATA"
        StrSql = StrSql & " WHERE (CountDayAmountSend > CountDayAmountRes)"
        If (p_StartDate <> "") And (p_StopDate <> "") Then
            StrSql = StrSql & " AND"
            StrSql = StrSql & " ("
            StrSql = StrSql & " ((Substring(InvReserveInfo.StartDate,1,10) >= '" & p_StartDate & "') AND (Substring(InvReserveInfo.StopDate,1,10) <= '" & p_StopDate & "'))"
            StrSql = StrSql & " AND "
            StrSql = StrSql & " ((Substring(InvReserveInfo.StartDate,1,10) >= '" & p_StartDate & "') AND (Substring(InvReserveInfo.StopDate,1,10) <= '" & p_StopDate & "'))"
            StrSql = StrSql & "  )"
        End If

        If (p_UnitCode <> "") Then
            StrSql = StrSql & " AND (UnitId = '" & p_UnitCode & "')"
        End If

        If (p_UserId <> "") Then
            StrSql = StrSql & " AND (UserId = '" & p_UserId & "')"
        End If

        StrSql = StrSql & " ORDER BY "
        StrSql = StrSql & " 	TMP_DATA.StartDate asc"

        Ds_SendCarLest = Wss.WebCustomBindData(StrSql)
        If Not Ds_SendCarLest Is Nothing Then
            iCountDs = Ds_SendCarLest.Tables(0).Rows.Count - 1
            For ii = 0 To iCountDs
                Ds_SendCarLest.Tables(0).Rows(ii).Item("UserName") = GetUserName(Ds_SendCarLest.Tables(0).Rows(ii).Item("UserId"))
                Ds_SendCarLest.Tables(0).Rows(ii).Item("UnitName") = GetUnitName(Ds_SendCarLest.Tables(0).Rows(ii).Item("UserId"))
            Next
            Ds_SendCarLest.Tables(0).AcceptChanges()
            GetData_SendCarLest = Ds_SendCarLest
        Else
            GetData_SendCarLest = Nothing
        End If

    End Function


End Module
